﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using MySql.Data.MySqlClient;
using System.Collections;
using System.Data;

namespace SmartProject.Data.MySQL
{
    public sealed class MySqlDataProvider : IDataProvider
    {
        private readonly List<MySqlParameter> _params;

        /// <summary>
        /// Creates a new instance of the DataHelper.
        /// </summary>
        public MySqlDataProvider()
        {
            _params = new List<MySqlParameter>();
        }

        /// <summary>
        /// Initializes the DataHelper class and acts as a reset function.
        /// </summary>
        public void Initialize()
        {
            _params.Clear();
        }

        /// <summary>
        /// This method is the same as Find, except, only a single row is returned.
        /// </summary>
        /// <param name="storedProcedure">Stored procedure name.</param>
        public Hashtable Load(string storedProcedure)
        {
            Hashtable values = new Hashtable();
            using (MySqlConnection connection = new MySqlConnection(DataProvider.ConnectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(storedProcedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(_params.ToArray());
                    MySqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        int count = reader.FieldCount;
                        while (reader.Read())
                        {
                            for (int i = 0; i < count; i++)
                            {
                                values.Add(reader.GetName(i), reader[i]);
                            }
                        }
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
            return values;
        }

        /// <summary>
        /// Runs a query using the stored procedure. The data is returned as
        /// a generic data table.
        /// </summary>
        /// <param name="storedProcedure">Stored procedure name.</param>
        /// <returns>Generic data table.</returns>
        public DataTable Find(string storedProcedure)
        {
            DataTable result = null;
            using (MySqlConnection connection = new MySqlConnection(DataProvider.ConnectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(storedProcedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(_params.ToArray());
                    MySqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        result = new DataTable();
                        int count = reader.FieldCount;
                        for (int i = 0; i < count; i++)
                        {
                            result.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
                        }
                        while (reader.Read())
                        {
                            DataRow row = result.NewRow();
                            for (int i = 0; i < count; i++)
                            {
                                row[i] = reader[i];
                            }
                            result.Rows.Add(row);
                        }
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
            return result;
        }

        /// <summary>
        /// Add a string-typed parameter.
        /// </summary>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="value">String value of parameter.</param>
        /// <param name="size">Length of the parameter.</param>
        public void Add(string name, string value, int size)
        {
            MySqlParameter param = new MySqlParameter(name, value);
            param.MySqlDbType = MySqlDbType.VarChar;
            param.Size = size;
            _params.Add(param);
        }

        public void Add(string name, DateTime value)
        {
            MySqlParameter param = new MySqlParameter(name, value);
            param.MySqlDbType = MySqlDbType.DateTime;
            _params.Add(param);
        }

        public void Add(string name, byte[] value)
        {
            MySqlParameter param = new MySqlParameter(name, value);
            param.MySqlDbType = MySqlDbType.Blob;
            _params.Add(param);
        }

        public void Add(string name, bool value)
        {
            MySqlParameter param = new MySqlParameter(name, value);
            param.MySqlDbType = MySqlDbType.Bit;
            _params.Add(param);
        }

        /// <summary>
        /// Converts the data parameter a SqlParameters for further execution.
        /// </summary>
        /// <param name="dataParameter">DataParameter to convert.</param>
        public void Add(DataParameter dataParameter)
        {
            switch (dataParameter.Type)
            {
                case DataType.DateTime:
                    Add(dataParameter.GetName(true), (DateTime)dataParameter.Value);
                    break;

                case DataType.Boolean:
                    Add(dataParameter.GetName(true), (Boolean)dataParameter.Value);
                    break;

                case DataType.Integer:
                    if (dataParameter.Value != null)
                    {
                        if (dataParameter.Value == DBNull.Value)
                        {
                            Add(dataParameter.GetName(true), -1);
                        }
                        else
                        {
                            Add(dataParameter.GetName(true), (int)dataParameter.Value);
                        }
                    }
                    else
                    {
                        Add(dataParameter.GetName(true), -1);
                    }
                    break;

                case DataType.String:
                    if (dataParameter.Value != null)
                    {
                        if (dataParameter.Value == DBNull.Value)
                        {
                            MySqlParameter param = new MySqlParameter(
                                dataParameter.GetName(true), DBNull.Value);
                            param.Size = dataParameter.Length;
                            param.MySqlDbType = MySqlDbType.VarChar;
                            _params.Add(param);
                        }
                        else
                        {
                            Add(dataParameter.GetName(true), (string)dataParameter.Value, dataParameter.Length);
                        }
                    }
                    else
                    {
                        Add(dataParameter.GetName(true), String.Empty, dataParameter.Length);
                    }
                    break;
                case DataType.Bytes:
                    if (dataParameter.Value != null)
                    {
                        Add(dataParameter.GetName(true), (byte[])dataParameter.Value);
                    }
                    break;

            }
        }

        /// <summary>
        /// Add a integer-typed parameter.
        /// </summary>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="value">Integer value of parameter.</param>
        public void Add(string name, int value)
        {
            MySqlParameter param;
            if (value == -1)
            {
                param = new MySqlParameter(name, DBNull.Value);
            }
            else
            {
                param = new MySqlParameter(name, value);
            }

            param.DbType = DbType.Int32;
            _params.Add(param);
        }

        private int _returnId;
        /// <summary>
        /// Gets the return row Id when you attempt to insert a new row.
        /// </summary>
        public int ReturnId
        {
            get { return _returnId; }
        }

        /// <summary>
        /// Executes a stored procedure. This is usually used when you are adding/ updating
        /// an item.
        /// </summary>
        /// <param name="storedProcedure">Stored procedure name.</param>
        public void Run(string storedProcedure)
        {
            _returnId = -1;
            using (MySqlConnection connection = new MySqlConnection(DataProvider.ConnectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(storedProcedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(_params.ToArray());
                    object value = command.ExecuteScalar();
                    if (value != null)
                    {
                        _returnId = Convert.ToInt32(value);
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
        }
    }
}
